package com.ziyear.dao.impl;

import com.ziyear.common.util.JDBCUtil;
import com.ziyear.dao.TrainSeatDao;
import com.ziyear.domain.TrainSeat;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class TrainSeatDaoImpl implements TrainSeatDao {
    private Connection conn = null;
    private PreparedStatement pstmt = null;
    private ResultSet rs = null;

    @Override
    public int add(TrainSeat trainSeat) throws SQLException {
        String sql = "insert into train_seat (trainid,carsnumber,seatnumber,seatuse,seattype,seatcode) values (?,?,?,?,?,?)";

        conn = JDBCUtil.getConnection();
        pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1, trainSeat.getTrainId());
        pstmt.setObject(2, trainSeat.getCarsNumber());
        pstmt.setObject(3, trainSeat.getSeatNumber());
        pstmt.setObject(4, trainSeat.getSeatUse());
        pstmt.setObject(5, trainSeat.getSeatType());
        pstmt.setObject(6, trainSeat.getSeatCode());
        int recordNumber = pstmt.executeUpdate();
        pstmt.close();
        JDBCUtil.close(conn);
        return recordNumber;
    }

    @Override
    public int delete(Integer trainId) throws SQLException {
        String sql = "delete from train_seat where trainid=?";
        conn = JDBCUtil.getConnection();
        pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1, trainId);
        int recordNumber = pstmt.executeUpdate();
        pstmt.close();
        JDBCUtil.close(conn);
        return recordNumber;
    }

    @Override
    public int occupySeat(Integer seatId) throws SQLException {
        String sql = "update train_seat set seatuse='USE' where seatid=? and seatuse='UNUSED'";
        conn = JDBCUtil.getConnection();
        pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1, seatId);
        int recordNumber = pstmt.executeUpdate();
        pstmt.close();
        JDBCUtil.close(conn);
        return recordNumber;
    }

    @Override
    public int freed(Integer seatId) throws SQLException {
        String sql = "update train_seat set seatuse='UNUSED' where seatid=? and seatuse='USE'";
        conn = JDBCUtil.getConnection();
        pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1, seatId);
        int recordNumber = pstmt.executeUpdate();
        pstmt.close();
        JDBCUtil.close(conn);
        return recordNumber;
    }

    //    //    seatid,trainid,carsnumber,seatnumber,seatuse,seattype,seatcode
    @Override
    public List<TrainSeat> queryUnUsedByTrainAndType(Integer trainId, String carsType) throws SQLException {
        String sql = "select * from train_seat where trainid=? and seattype=? and seatuse='UNUSED'";
        conn = JDBCUtil.getConnection();
        pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1, trainId);
        pstmt.setObject(2, carsType);
        rs = pstmt.executeQuery();
        List<TrainSeat> list = new ArrayList<>();
        while (rs.next()) {
            int seatid = rs.getInt("seatid");
            int trainid = rs.getInt("trainid");
            int carsnumber = rs.getInt("carsnumber");
            String seatnumber = rs.getString("seatnumber");
            String seatuse = rs.getString("seatuse");
            String seattype = rs.getString("seattype");
            String seatcode = rs.getString("seatcode");
            TrainSeat trainSeat = new TrainSeat(seatid, trainId, carsnumber, seattype, seatcode, seatnumber, seatuse);
            list.add(trainSeat);
        }
        rs.close();
        pstmt.close();
        JDBCUtil.close(conn);
        return list;
    }

    @Override
    public List<TrainSeat> queryUnUsedByTrainAndTypeAndCode(Integer trainId, String carsType, String seatCode) throws SQLException {
        String sql = "select * from train_seat where trainid=? and seattype=? and seatcode=? and seatuse='UNUSED'";
        conn = JDBCUtil.getConnection();
        pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1, trainId);
        pstmt.setObject(2, carsType);
        pstmt.setObject(3, seatCode);
        rs = pstmt.executeQuery();
        List<TrainSeat> list = new ArrayList<>();
        while (rs.next()) {
            int seatid = rs.getInt("seatid");
            int trainid = rs.getInt("trainid");
            int carsnumber = rs.getInt("carsnumber");
            String seatnumber = rs.getString("seatnumber");
            String seatuse = rs.getString("seatuse");
            String seattype = rs.getString("seattype");
            String seatcode = rs.getString("seatcode");
            TrainSeat trainSeat = new TrainSeat(seatid, trainid, carsnumber, seattype, seatcode, seatnumber, seatuse);
            list.add(trainSeat);
        }
        rs.close();
        pstmt.close();
        JDBCUtil.close(conn);
        return list;
    }

    @Override
    public TrainSeat queryByIdAndLock(Integer seatId) throws SQLException {
        String sql = "select * from train_seat where seatid=? for update";
        conn = JDBCUtil.getConnection();
        pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1, seatId);
        rs = pstmt.executeQuery();
        TrainSeat trainSeat = null;
        while (rs.next()) {
            int seatid = rs.getInt("seatid");
            int trainid = rs.getInt("trainid");
            int carsnumber = rs.getInt("carsnumber");
            String seatnumber = rs.getString("seatnumber");
            String seatuse = rs.getString("seatuse");
            String seattype = rs.getString("seattype");
            String seatcode = rs.getString("seatcode");
            trainSeat = new TrainSeat(seatid, trainid, carsnumber, seattype, seatcode, seatnumber, seatuse);
        }
        rs.close();
        pstmt.close();
        JDBCUtil.close(conn);
        return trainSeat;
    }

    @Override
    public void add(List<TrainSeat> trainSeats) throws SQLException {
        for (TrainSeat trainSeat : trainSeats) {
            add(trainSeat);
        }
    }
}
